﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Diagnostics;
using Utils;
using Utils.Extensions;

namespace StockModel
{
	[DebuggerDisplay("Connection = {_dbClient.ConnectionString}")]
	public class QuotationDataSource : DataSourceBase
	{
		#region Attributes

		private readonly StringCollection _tableCache;

		#endregion

		#region Constants

		private const string TableFormat = "{0}-{1}{2}{3}";
		private const string ClosingQuotationQuery = "SELECT TOP 1 *  FROM [{0}] WITH(NOLOCK) ORDER BY [Date] DESC";
		private const string QuotationTableInsert = "INSERT INTO QuotationTables VALUES(CONVERT(date, '{0}', 103), {1}, '{2}')";
		private const string StockIdQuery = "SELECT Id FROM Stocks WITH(NOLOCK) WHERE Symbol = @Symbol";
		private const string StockQuery = "SELECT * FROM [{0}] WITH(NOLOCK) ORDER BY [Date]";
		private const string AvailableDaysStockQuery = "SELECT Day FROM [QuotationTables] WITH(NOLOCK) WHERE IdStock = {0}";
		private const string AvailableDaysQuery = "SELECT DISTINCT(Day) FROM [QuotationTables] WITH(NOLOCK) ORDER BY Day";
		private const string QuotationInsert = "INSERT INTO [{0}] VALUES (CONVERT(datetime2, '{1}', 103), @LastValue, @Volume, @Offer1Value, @Offer1Volume, @Request1Value, @Request1Volume)";
		private const string CreateQuotationTableQuery = "CREATE TABLE [dbo].[{0}](" +
														 "[Date] [datetime2](2) NOT NULL PRIMARY KEY," +
														 "[LastValue] [decimal](9, 4) NOT NULL," +
														 "[Volume] [int] NOT NULL," +
														 "[Offer1Value] [decimal](9, 4) NOT NULL," +
														 "[Offer1Volume] [int] NOT NULL," +
														 "[Request1Value] [decimal](9, 4) NOT NULL," +
														 "[Request1Volume] [int] NOT NULL); " +
														 "ALTER TABLE [{0}] ADD CONSTRAINT [Chk_{0}] CHECK ([Date] >= CONVERT(datetime2, '{1}', 103))";

		#endregion

		#region Public Methods

		public QuotationDataSource(IDataBaseClient dbClient) : base(dbClient)
		{
			_tableCache = new StringCollection();
		}

		public void Save(Stock stock, Quotation quotation)
		{
			string tableName = QuotationDataSource.GetQuotationTableName(stock, quotation.Acquisition);

			if (!this.ExistsQuotationTable(tableName))
			{
				int stockId = Convert.ToInt32(base.DataBaseClient.ExecuteScalar(StockIdQuery, new string[] { "@Symbol" }, new object[] { stock.Id.Code }));
				base.DataBaseClient.ExecuteCommand(string.Format(CreateQuotationTableQuery, tableName, quotation.Acquisition.ToString("dd/MM/yyyy")));
				base.DataBaseClient.ExecuteCommand(string.Format(QuotationTableInsert, quotation.Date, stockId, tableName));
			}

			base.DataBaseClient.ExecuteCommand(string.Format(QuotationInsert, tableName, quotation.Acquisition.ToString("dd/MM/yyyy HH:mm:ss:fff")),
										new string[] { "@LastValue", "@Volume", "@Offer1Value", "@Offer1Volume", "@Request1Value", "@Request1Volume" },
										new object[] { quotation.Value, quotation.Volume, quotation.Buyers[0].Value, quotation.Buyers[0].Quantity, quotation.Sellers[0].Value, quotation.Sellers[0].Quantity });
		}

		public bool ExistDay(Stock stock, DateTime day)
		{
			string tableName = QuotationDataSource.GetQuotationTableName(stock, day);
			return this.ExistsQuotationTable(tableName);
		}

		public List<DateTime> GetAvailableDays(Stock stock)
		{
			List<DateTime> result = new List<DateTime>();

			int stockId = Convert.ToInt32(base.DataBaseClient.ExecuteScalar(StockIdQuery, new string[] { "@Symbol" }, new object[] { stock.Id.Code }));
			DataTable data = base.DataBaseClient.ExecuteQuery(string.Format(AvailableDaysStockQuery, stockId));

			foreach (DataRow row in data.Rows)
			{
				result.Add(DateTime.Parse(row[0].ToString()));
			}

			return result;
		}

		public List<DateTime> GetAvailableDays()
		{
			List<DateTime> result = new List<DateTime>();

			DataTable data = base.DataBaseClient.ExecuteQuery(AvailableDaysQuery);

			foreach (DataRow row in data.Rows)
			{
				result.Add(DateTime.Parse(row[0].ToString()));
			}

			return result;
		}

		public IEnumerable<Quotation> GetQuotations(Stock stock, DateTime day, ExchangeMarket market)
		{
			string tableName = QuotationDataSource.GetQuotationTableName(stock, day);

			if (this.ExistsQuotationTable(tableName))
			{
				DataTable dataRead = base.DataBaseClient.ExecuteQuery(string.Format(StockQuery, tableName));
				List<Quotation> tempList = new List<Quotation>();

				foreach (DataRow row in dataRead.Rows)
				{
					Quotation quotation = new Quotation() { Acquisition = (DateTime)row["Date"], Value = (decimal)row["LastValue"], Volume = (int)row["Volume"] };
					quotation.Buyers.Add(new Order((decimal)row["Offer1Value"], (int)row["Offer1Volume"]));
					quotation.Sellers.Add(new Order((decimal)row["Request1Value"], (int)row["Request1Volume"]));
					quotation.Type = market.GetQuoteType(quotation.Acquisition);
					tempList.Add(quotation);
				}

				return QuotationDataSource.ConvertDbQuotations(tempList, market);
			}
			else
			{
				throw new InvalidOperationException(string.Format("Tried to load quotations for day {0} of the stock {1} which do not exist. Check first its existence with ExistsDay()", day, stock.Id.Code));
			}
		}

		// This call will return null if there is no quotation
		public Quotation GetClosedQuotation(Stock stock, DateTime day, ExchangeMarket market)
		{
			Quotation result = null;

			string tableName = QuotationDataSource.GetQuotationTableName(stock, day);

			if (this.ExistsQuotationTable(tableName))
			{
				DataTable dataRead = base.DataBaseClient.ExecuteQuery(string.Format(ClosingQuotationQuery, tableName));

				if (dataRead.Rows.Count == 1)
				{
					DataRow row = dataRead.Rows[0];

					result = new Quotation() { Acquisition = (DateTime)row["Date"], Value = (decimal)row["LastValue"], Volume = (int)row["Volume"] };
					result.Buyers.Add(new Order((decimal)row["Offer1Value"], (int)row["Offer1Volume"]));
					result.Sellers.Add(new Order((decimal)row["Request1Value"], (int)row["Request1Volume"]));
					result.Type = market.GetQuoteType(result.Acquisition);

					if (result.Type != QuoteType.Closed)
					{
						result = null;
					}
				}
				else
				{
					Debug.WriteLine("The day {0} of the stock {1} has no quotations", day, stock.Id.Code);
				}
			}
			else
			{
				throw new InvalidOperationException(string.Format("Tried to load quotations for day {0} of the stock {1} which do not exist. Check first its existence with ExistsDay()", day, stock.Id.Code));
			}

			return result;
		}

		#endregion

		#region Private Methods

		private static IEnumerable<Quotation> ConvertDbQuotations(IEnumerable<Quotation> dbList, ExchangeMarket market)
		{
			List<Quotation> result = new List<Quotation>();
			Quotation previousQuotation = null;

			foreach (Quotation quotation in dbList)
			{
				quotation.Date = quotation.Acquisition.RemoveMilliSeconds();

				if (previousQuotation == null)
				{
					result.Add(quotation);
				}
				else
				{
					result.AddRange(QuotationCollectionFiller.CreateOneSecondSpacedCollection(previousQuotation, quotation, market));
				}
				previousQuotation = quotation;
			}

			return result;
		}

		private static string GetQuotationTableName(Stock stock, DateTime day)
		{
			return string.Format(TableFormat, stock.Id.Code, day.Date.Year.ToString("0000"), day.Date.Month.ToString("00"), day.Date.Day.ToString("00"));
		}

		private bool ExistsQuotationTable(string tableName)
		{
			bool result;

			if (_tableCache.Contains(tableName))
			{
				result = true;
			}
			else if (base.DataBaseClient.ExistsTable(tableName))
			{
				_tableCache.Add(tableName);
				result = true;
			}
			else
			{
				result = false;
			}

			return result;
		}

		#endregion
	}
}
